from scipy.optimize import minimize
import openpyxl
from collections import OrderedDict


workbook = openpyxl.load_workbook('data/算法大作业数据.xlsx')

"""----------------------饲料原料及其营养成分含量----------------------"""
worksheet = workbook['饲料原料及其营养成分含量']
rows = []
for row in worksheet.iter_rows(values_only=True): 
    # if "%" in row[0]:
    rows.append(row)
cols = zip(*rows)
feed_data = {col[0]: col[1:15] for col in cols}



"""----------------------生猪饲养标准----------------------"""
worksheet = workbook['生猪饲养标准']
rows = []
for row in worksheet.iter_rows(values_only=True): rows.append(row)

del rows[0]
cols = zip(*rows)
objects = [_ for _ in cols]
del objects[0]
del objects[-1]

# 饲料数据
feed_data = {
    '玉米': [3.8, 8.0, 0.2, 0.5, 4.0, 0.4, 0.3, 0.01, 0.05, 0.1, 0.2, 0.3, 0.01, 0.6],
    '麦麸': [2.5, 16.0, 0.1, 0.3, 0.2, 0.1, 0.05, 0.02, 0.07, 0.09, 0.12, 0.15, 0.03, 0.4],
    # 其他饲料材料的营养成分数据
}

# 生长阶段
growth_stages = {
    '20-50kg': {
        'energy_requirement': 1000,
        # 其他营养需求
    },
    '50-80kg': {
        'energy_requirement': 1200,
        # 其他营养需求
    },
    '80-120kg': {
        'energy_requirement': 1500,
        # 其他营养需求
    }
}

# 用料上限和下限
upper_limits = {
    '玉米': 0.5,
    '麦麸': 0.3,
    # 其他饲料材料的上限
}

lower_limits = {
    '玉米': 0.1,
    '麦麸': 0.05,
    # 其他饲料材料的下限
}

def objective(x):
    # 计算总价格
    total_price = sum([price * x[i] for i, price in enumerate(feed_prices)])
    return total_price

def energy_constraint(x):
    constraints = []
    for stage in growth_stages:
        energy_requirement = growth_stages[stage]['energy_requirement']
        energy_content = sum([feed_data[feed_name][0] * x[i] for i, feed_name in enumerate(feed_names)])
        constraints.append(energy_content - energy_requirement)
    return constraints

def ratio_constraints(x):
    constraints = []
    for i, feed_name in enumerate(feed_names):
        constraints.append(x[i] - upper_limits[feed_name])
        constraints.append(lower_limits[feed_name] - x[i])
    return constraints

# 初始化变量
feed_names = list(feed_data.keys())
feed_prices = [10, 20, 30, ...]  # 饲料价格
initial_guess = [0.2, 0.3, ...]  # 初始猜测

# 定义约束条件
constraints = [{'type': 'eq', 'fun': energy_constraint}, {'type': 'ineq', 'fun': ratio_constraints}]

# 求解最优化问题
solution = minimize(objective, initial_guess, constraints=constraints)

# 输出结果
print(f"目标函数值（最小价格）: {solution.fun}")
print("最优饲料配方:")
for i, feed_name in enumerate(feed_names):
    print(f"{feed_name}: {solution.x[i]}")
